package com.jsyl.dao.authority;

import com.jsyl.dao.base.BaseDao;
import com.jsyl.dao.base.SqlBuilder;
import com.jsyl.entity.authority.Child;
import com.jsyl.entity.authority.ClassSchedule;
import com.jsyl.infra.utils.page.PageModel;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * // **孩子Dao
 */
@Repository
public class ChildDao extends BaseDao<Child> {


    @Override
    public PageModel<Child> query(PageModel<Child> pm, Child child) {
        StringBuilder sql = new StringBuilder();
        StringBuilder totalsql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append("select c.id as ID,c.name as NAME,c.sex as SEX,c.year as YEAR,c.month as MONTH ,c.day as DAY,c.center_id as CENTERID,c.grade as GRADE,c.create_time as CREATETIME,\n" +
                "c.modify_time as MODIFYTIME ,c.center_id as CENTERID ,c.class_times as CALSSTIME,sc.teacher_id as TEACHERID,CASE WHEN  c.year=\"\" THEN date_format(now(),'%Y') WHEN  c.year is null THEN date_format(now(),'%Y')  ELSE  c.year END AS YEARS,CASE WHEN  c.month=\"\" THEN date_format(now(),'%m') WHEN  c.month is null THEN date_format(now(),'%m') ELSE  c.month END AS MONTHS  ").append(SqlBuilder.SQL_FROM);
        totalsql.append(SqlBuilder.SQL_SELECT).append(" count(1) from (SELECT  distinct c.id ").append(SqlBuilder.SQL_FROM);
        whereSql.append(
                " schedule_detail_child sc left join child c on c.id=sc.child_id ")
                .append(SqlBuilder.SQL_WHERE).append("1=1 ");

        Map<String, Object> paramMap = new HashMap<String, Object>();
        if (StringUtils.isNotEmpty(child.getTeacherId())) {
            whereSql.append(SqlBuilder.SQL_AND).append("sc.teacher_id").append(" = ").append(":teacher_id");
            paramMap.put("teacher_id", child.getTeacherId());
        }

        if (null != child.getCenterId()) {
            whereSql.append(SqlBuilder.SQL_AND).append("c.center_id").append(" = ").append(":center_id");
            paramMap.put("center_id", child.getCenterId());
        }
        if (StringUtils.isNotEmpty(child.getName())) {
            whereSql.append(SqlBuilder.SQL_AND).append("c.name").append(SqlBuilder.SQL_LIKE).append(":childName");
            paramMap.put("childName", "%" + child.getName() + "%");
        }
        totalsql.append(whereSql).append(" ) a");
        whereSql.append(" GROUP BY c.id  order by YEARS desc ,CAST(MONTHS as SIGNED) desc");
        logger.info("totalsql : " + "\"" + totalsql.toString() + "\"");
        Integer totalCount = namedTemplate.queryForObject(totalsql.toString(), paramMap, Long.class).intValue();


        Integer startIndex = pm.getStartIndex();
        Integer pageSize = pm.getPageSize();

        if (pageSize == null || pageSize <= 0) {
            pageSize = PageModel.DEFAULT_PAGE_SIZE;
        }

        if (startIndex == null || startIndex < 0) {
            startIndex = 0;
        } else if (startIndex > totalCount) {
            Integer totalPage = (int) Math.ceil(totalCount.doubleValue() / pageSize.doubleValue());
            startIndex = (totalPage - 1) * pageSize;
        }

        whereSql.append(SqlBuilder.SQL_LIMIT);
        if (startIndex != null && startIndex >= 0) {
            whereSql.append(startIndex).append(",");
        }
        whereSql.append(pageSize);

        sql.append(whereSql);

        logger.info("SQL : " + "\"" + sql.toString() + "\"");
        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        Map<String, String> columnMapper = new HashMap<>();
        List<Child> list = new ArrayList<Child>();
        Child vo = null;
        for (Iterator<Map<String, Object>> iterator = resultList.iterator(); iterator.hasNext(); ) {
            Map<String, Object> record = iterator.next();
            vo = new Child();
            vo.setId((Integer) record.get("ID"));

            Integer firstAttend = null;
            Integer classTimes = null;
            String teacherId = "";
            Integer centerId = null;
            String modifyTime = "";
            String createTime = "";
            String grade = "未设置";
            String day = "2016";
            String month = "11";
            String year = "01";
            String sex = "";
            String name = "";
            String birthday = "无生日";
            String firstAttendStr = "否";
            String age = "0岁";
            String birthdayval = "20161001";

            if (!StringUtils.isEmpty((String) record.get("NAME"))) {
                name = (String) record.get("NAME");
            }
            if (record.get("CALSSTIME") != null) {
                classTimes = (Integer) record.get("CALSSTIME");
            }

            if (!StringUtils.isEmpty((String) record.get("TEACHERID"))) {
                teacherId = (String) record.get("TEACHERID");
            }


            if (null != (Integer) record.get("CENTERID")) {
                centerId = (Integer) record.get("CENTERID");
            }

            if (!StringUtils.isEmpty((String) record.get("MODIFYTIME"))) {
                modifyTime = (String) record.get("MODIFYTIME");
                if (!modifyTime.equals("")) {
                    modifyTime = formattingDate(modifyTime);
                }
            }

            if (!StringUtils.isEmpty((String) record.get("CREATETIME"))) {
                createTime = (String) record.get("CREATETIME");
                if (!createTime.equals("") && createTime!=null) {
                    createTime = formattingDate(createTime);
                }

            }
            if (!StringUtils.isEmpty((String) record.get("GRADE"))) {
                String gardeStr = (String) record.get("GRADE");
                if (gardeStr.equals("")||gardeStr.equals("0")){
                    grade = "学前";
                }else {
                    grade = (String) record.get("GRADE")+"年级";
                }
            }

            if (!StringUtils.isEmpty((String) record.get("DAY"))) {
                day = (String) record.get("DAY");
            }
            if (!StringUtils.isEmpty((String) record.get("MONTH"))) {
                month = (String) record.get("MONTH");
            }
            if (!StringUtils.isEmpty((String) record.get("YEAR"))) {
                year = (String) record.get("YEAR");
            }
            if (!StringUtils.isEmpty((String) record.get("SEX"))) {
                sex = (String) record.get("SEX");
            }

            if (!year.equals("") && !month.equals("") && !day.equals("")) {
                birthday = year + "年" + month + "月" + day + "日";
                birthdayval = year + month + day;
            }
            //年龄
            if (!year.equals("") && !month.equals("")) {
                birthday = year + month;
                try {
                    age = getAge(birthday);
                } catch (ParseException e) {
                    age = "0岁";
                }
            }


            vo.setClassTimes(classTimes);
            vo.setBirthday(birthday);
            vo.setFirstAttendStr(firstAttendStr);
            vo.setAge(age);
            vo.setDay(day);
            vo.setYear(year);
            vo.setModifyTime(modifyTime);
            vo.setMonth(month);
            vo.setTeacherId(teacherId);
            vo.setFirstAttend(firstAttend);
            vo.setGrade(grade);
            vo.setSex(sex);
            vo.setName(name);
            vo.setCreateTime(createTime);
            vo.setCenterId(centerId);
            list.add(vo);
        }
        pm.setTotalCount(totalCount);
        pm.setPageData(list);

        return pm;

    }


    private String formattingDate(String strDate) {
        // 准备第一个模板，从字符串中提取出日期数字
        String pat1 = "yyyyMMddHHmmss";
        // 准备第二个模板，将提取后的日期数字变为指定的格式
        String pat2 = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat sdf1 = new SimpleDateFormat(pat1);        // 实例化模板对象
        SimpleDateFormat sdf2 = new SimpleDateFormat(pat2);        // 实例化模板对象
        Date d = null;
        String data = "没有日期";
        try {
            d = sdf1.parse(strDate);   // 将给定的字符串中的日期提取出来
        } catch (Exception e) {            // 如果提供的字符串格式有错误，则进行异常处理
            e.printStackTrace();       // 打印异常信息
        }
        try {
             data = sdf2.format(d);   // 将给定的字符串中的日期提取出来
        } catch (Exception e) {            // 如果提供的字符串格式有错误，则进行异常处理
           return data = "没有日期";      // 打印异常信息
        }
        return data;
    }

    public String getAge(String birthday) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        Date date = new Date();
        String nowdata = sdf.format(date);
        Calendar bef = Calendar.getInstance();
        Calendar aft = Calendar.getInstance();
        bef.setTime(sdf.parse(birthday));
        aft.setTime(sdf.parse(nowdata));
        int result = aft.get(Calendar.MONTH) - bef.get(Calendar.MONTH);
        int month = (aft.get(Calendar.YEAR) - bef.get(Calendar.YEAR)) * 12;
        int moth = Math.abs(month + result);
        int yearAge = moth / 12;
        int mothAge = moth % 12;
        String age = yearAge + "岁" + mothAge + "个月";
        return age;
    }

    public List<Child> getSearchChildData(Child child) {
        StringBuilder sql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append(SqlBuilder.SQL_SELECT).append("select c.id as ID,c.name as NAME,c.center_id as CENTERID ,sc.teacher_name as TEACHERNAME").append(SqlBuilder.SQL_FROM);
        whereSql.append("schedule_detail_child sc left join child c on c.id=sc.child_id")
                .append(SqlBuilder.SQL_WHERE).append("1=1");
        Map<String, Object> paramMap = new HashMap<String, Object>();
        if (StringUtils.isNotEmpty(child.getTeacherId())) {
            whereSql.append(SqlBuilder.SQL_AND).append("sc.teacher_id").append(" = ").append(":teacher_id");
            paramMap.put("teacher_id", child.getTeacherId());
        }
        if (null != child.getCenterId()) {
            whereSql.append(SqlBuilder.SQL_AND).append("c.center_id").append(" = ").append(":center_id");
            paramMap.put("center_id", child.getCenterId());
        }
        sql.append(whereSql);
        logger.info("SQL : " + "\"" + sql.toString() + "\"");

        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        List<Child> list = new ArrayList<Child>();
        for (int i = 0; i < resultList.size(); i++) {
            Child child1 = new Child();
            child1.setName((String) resultList.get(i).get("NAME"));
            child1.setCenterName((String) resultList.get(i).get("TEACHERNAME"));
            list.add(child1);
        }
        return list;
    }

}
